package com.todo.pro.auditlog.handler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.todo.pro.auditlog.domain.AuditLog;

public abstract class AbstractSQLAuditHandler extends AbstractSQLHandler {

	private final String auditLogInsertSQL = "insert into %s "
			+ "(table_name, column_name, primary_key, parent_id, new_value, old_value, operation, create_time, create_clerk) "
			+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private Date insertTime = null;

	private DBMetaDataHolder dbMetaDataHolder;

	public AbstractSQLAuditHandler(Connection connection, DBMetaDataHolder dbMetaDataHolder, String sql) {
		super(connection, sql);
		this.dbMetaDataHolder = dbMetaDataHolder;
	}

	public void saveAuditLog(List<List<AuditLog>> auditLogList, Long noClerkId) {
		try {
			boolean autoCommit = getConnection().getAutoCommit();
			if (autoCommit) {
				getConnection().setAutoCommit(false);
			}
			insertTime = new Date();
			for (List<AuditLog> auditLogs : auditLogList) {
				if (auditLogs.size() > 0) {
					Long parentID = saveAuditLog(auditLogs.get(0), noClerkId);
					auditLogs.remove(0);
					saveAuditLogList(auditLogs, parentID, noClerkId);
				}
			}
			if (autoCommit) {
				getConnection().commit();
				getConnection().setAutoCommit(true);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void saveAuditLog(Map<String, List<List<AuditLog>>> auditLogListMap, Long noClerkId) {
		List<List<AuditLog>> auditLogList = new ArrayList<>();
		for (List<List<AuditLog>> lists : auditLogListMap.values()) {
			auditLogList.addAll(lists);
		}
		saveAuditLog(auditLogList, noClerkId);
	}

	public void saveAuditLogList(List<AuditLog> auditLogs, Long parentID, Long noClerkId) {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = getConnection().prepareStatement(String.format(auditLogInsertSQL,
					dbMetaDataHolder.getAuditLogTableCreator().getCurrentValidTableName()));
			for (AuditLog auditLog : auditLogs) {
				int i = 1;
				preparedStatement.setObject(i++, auditLog.getTableName());
				preparedStatement.setObject(i++, auditLog.getColumnName());
				preparedStatement.setObject(i++, auditLog.getPrimaryKey());
				preparedStatement.setObject(i++, parentID);
				preparedStatement.setObject(i++, auditLog.getNewValue());
				preparedStatement.setObject(i++, auditLog.getOldValue());
				preparedStatement.setObject(i++, auditLog.getOperation());
				preparedStatement.setObject(i++, insertTime);
				try {
					preparedStatement.setObject(i, noClerkId);
				} catch (RuntimeException e) {
					preparedStatement.setObject(i, noClerkId);
				}
				preparedStatement.addBatch();
			}
			preparedStatement.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private Long saveAuditLog(AuditLog auditLog, Long noClerkId) {
		Long resultId = null;
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = getConnection().prepareStatement(
					String.format(auditLogInsertSQL,
							dbMetaDataHolder.getAuditLogTableCreator().getCurrentValidTableName()),
					Statement.RETURN_GENERATED_KEYS);
			int i = 1;
			preparedStatement.setObject(i++, auditLog.getTableName());
			preparedStatement.setObject(i++, auditLog.getColumnName());
			preparedStatement.setObject(i++, auditLog.getPrimaryKey());
			preparedStatement.setObject(i++, null);
			preparedStatement.setObject(i++, auditLog.getNewValue());
			preparedStatement.setObject(i++, auditLog.getOldValue());
			preparedStatement.setObject(i++, auditLog.getOperation());
			preparedStatement.setObject(i++, insertTime);
			try {
				preparedStatement.setObject(i, noClerkId);
			} catch (RuntimeException e) {
				preparedStatement.setObject(i, noClerkId);
			}
			int affectRows = preparedStatement.executeUpdate();
			if (affectRows > 0) {
				ResultSet resultSet = preparedStatement.getGeneratedKeys();
				if (resultSet.next())
					resultId = resultSet.getLong(1);
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return resultId;
	}

	public DBMetaDataHolder getDbMetaDataHolder() {
		return dbMetaDataHolder;
	}
}